# coding:utf-8
import re
import os
import pymysql
from db_config import DBConfig

db_conf = DBConfig()

class Competitor:
    def __init__(self):
        pass

    def update_business(self):
        mysql_conn = self.getDevMysqlConn();
        cur = mysql_conn.cursor()
        sql = '''Select * from com_basic_info where major_product_type != '' '''
        cur.execute(sql)
        company_list = cur.fetchall()
        for company in company_list:
            com_uni_code = company[0]
            com_name = company[1]
            businesses = []
            if company[10]:
                businesses = company[10].split('、')
            for business in businesses:
                business = business.replace("'", "")
                sql = '''SELECT  * FROM kg_com_business 
                          WHERE com_uni_code=%s 
                          AND com_name = '%s' 
                          AND  com_business = '%s' ''' % (com_uni_code, com_name, business)
                cur.execute(sql)
                result = cur.fetchone()
                if len(result) > 0:
                    continue
                sql = '''insert into kg_com_business (com_uni_code,com_name,com_business) 
                          VALUE (%s,'%s','%s')''' % (com_uni_code, com_name, business)
                print(sql)
                cur.execute(sql)
        mysql_conn.commit()
        cur.close()
        mysql_conn.close()

    def update_product(self):
        mysql_conn = self.getDevMysqlConn();
        cur = mysql_conn.cursor()
        sql = '''Select * from com_basic_info where major_product_name != '' '''
        cur.execute(sql)
        company_list = cur.fetchall()
        for company in company_list:
            com_uni_code = company[0]
            com_name = company[1]
            products = []
            if company[11]:
                products = company[11].split('、')
            for product in products:
                product = product.replace("'", "")
                sql = '''SELECT * FROM kg_com_product 
                          WHERE com_uni_code=%s 
                          AND com_name = '%s' 
                          AND  com_product = '%s' ''' % (com_uni_code, com_name, product)
                cur.execute(sql)
                result = cur.fetchone()
                if result is not None:
                    continue
                sql = '''insert into kg_com_product (com_uni_code,com_name,com_product) 
                          VALUE (%s,'%s','%s')''' % (com_uni_code, com_name, product)
                print(sql)
                cur.execute(sql)
        mysql_conn.commit()
        cur.close()
        mysql_conn.close()

    def update_competitor_business(self):
        mysql_conn = self.getDevMysqlConn();
        cur = mysql_conn.cursor()
        sql = '''select * from com_basic_info where major_product_type != '' '''
        cur.execute(sql)
        company_list = cur.fetchall()
        for company_a in company_list:
            sql = '''select * from kg_com_business where com_uni_code = %s''' % company_a[0]
            print(sql)
            cur.execute(sql)
            a_businesses = cur.fetchall()
            for company_b in company_list:
                if company_a[0] == company_b[0]:
                    continue
                sql = '''select * from kg_com_business where com_uni_code = %s''' % company_b[0]
                cur.execute(sql)
                b_businesses = cur.fetchall()
                for a_business in a_businesses:
                    for b_business in b_businesses:
                        if a_business[3] == b_business[3]:
                            sql = '''insert into kg_com_business_competive (com_uni_code_competive,com_uni_code_bycompetive,business_id,bybusiness_id) 
                                    value ('%s','%s','%s','%s')''' % (a_business[1], b_business[1], a_business[0], b_business[0])
                            print(sql)
                            cur.execute(sql)
        mysql_conn.commit()
        cur.close()
        mysql_conn.close()


    def update_competitor_product(self):
        mysql_conn = self.getDevMysqlConn();
        cur = mysql_conn.cursor()
        sql = '''select * from com_basic_info where major_product_name != '' '''
        cur.execute(sql)
        company_list = cur.fetchall()
        for company_a in company_list:
            sql = '''select * from kg_com_product where com_uni_code = %s''' % company_a[0]
            print(sql)
            cur.execute(sql)
            a_products = cur.fetchall()
            for company_b in company_list:
                if company_a[0] == company_b[0]:
                    continue
                sql = '''select * from kg_com_product where com_uni_code = %s''' % company_b[0]
                cur.execute(sql)
                b_products = cur.fetchall()
                for a_product in a_products:
                    for b_product in b_products:
                        if a_product[3] == b_product[3]:
                            sql = '''insert into kg_com_proudct_competive (com_uni_code_competive,com_uni_code_bycompetive,product_id,byproduct_id) 
                                    value ('%s','%s','%s','%s')''' % (a_product[1], b_product[1], a_product[0], b_product[0])
                            print(sql)
                            cur.execute(sql)
        mysql_conn.commit()
        cur.close()
        mysql_conn.close()



    # 获取开发mysql数据库连接
    def getDevMysqlConn(self):
        host, port, dbname, user_name, password = db_conf.get_mysql_dev_conn()
        conn = pymysql.connect(
            host=host,
            port=int(port),
            user=user_name,
            passwd=password,
            db=dbname,
            charset='utf8',
        )
        return conn

if __name__ == "__main__":
    c = Competitor()
    # c.update_business()
    # c.update_product()
    # c.update_competitor_business()
    c.update_competitor_product()